local sqlite3 = require("lsqlite3")
require"luaFDTD3D"
local nl = require"luaNumeric"
local rcwa = require"luaRCWA"
local plot = require "Q2DViewerEx"
require "unity"


-- require "Q2DViewer"
-- require "Q2DViewerEx"
-----------------------------------------------------------------------------
--初始化参数
local DataSheet={
      
     ------------------------------------------------------
     --所有数据，存储维度：1D 
     db = {};
     ------------------------------------------------------
     
     ------------------------------------------------------
     --所有数据，存储维度：1D
     --横坐标：索引，纵坐标：数值
     data_list = {};  --key：索引， value：数据
     ------------------------------------------------------
     
     ------------------------------------------------------
     --索引名称： 例如： { "freq", "theta" }
     keys = {};
     ------------------------------------------------------

     ------------------------------------------------------
     --每一个维度的数据
     --例如： { freq={1,2}, theta={0,180} }
     index = {};     --key: 名称, value: vector
     ------------------------------------------------------
     

     insertStmt ={};
     
     insertFun = {};
     console = false;
};
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
--创建表和创建元表
function DataSheet:new (o)
	o = o or {};
	self.__index = self;	
	setmetatable(o,self)
	return o;
end
-----------------------------------------------------------------------------


-----------------------------------------------------------------------------
--创建数据库,并初始化
function DataSheet:init (path)
	self.path = path;
	if self.path then
		self.db  = assert( sqlite3.open(path)  ) 
	else
		self.db = sqlite3.open_memory()
	end
	self.db:exec[[CREATE TABLE keys (id INTEGER PRIMARY KEY,content)]]
	self.create_key = self.db:prepare[[ INSERT INTO keys VALUES (:key,:value) ]]
end
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
-- 添加变量
--add key index
function DataSheet:add_key_index(key,value)
	self.insertStmt[key]:bind_names{ key=nil, value = value    }
	self.insertStmt[key]:step()
	self.insertStmt[key]:reset()
end
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
-- 生成数据表 
--add key index
function DataSheet:create_data_table()
	self.keys = {}
	for k,v in pairs (self.insertStmt) do
		table.insert(self.keys, k);
	end
	table.sort( self.keys, function (a,b) return a>b; end )
	
	local str = 'CREATE TABLE data (id INTEGER PRIMARY KEY';
	for k,v in pairs (self.keys )do
		str = str .. ', ' .. v;
	end
	str = str .. ', value)';
	print(str)
	assert(self.db:exec(str))
	
	str = ' INSERT INTO data VALUES (NULL,';
	for k,v in pairs (self.keys )do
		str = str ..':' .. v  .. ', ';
	end
	str = str..':value)';
	print(str)
	self.addDataStmt =  assert(self.db:prepare(str));
end
-----------------------------------------------------------------------------


-----------------------------------------------------------------------------
function DataSheet:add_data(data)
	self.addDataStmt:bind_names( data )
	self.addDataStmt:step()
	self.addDataStmt:reset()
end
-----------------------------------------------------------------------------


-----------------------------------------------------------------------------
function DataSheet:close()
	self.db:close()
end
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
function DataSheet:print_keys()
	for row in self.db:nrows("SELECT * FROM keys") do
		print(row.id, row.content)
	end
end
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
--
function DataSheet:key_index(key,value)
	local stmt =  assert(self.db:prepare('SELECT content FROM '..key..' WHERE content = ?'))
	stmt:bind_values(value)
	local indx;
	while (stmt:step() == sqlite3.ROW) do
		print( stmt:get_uvalues() )
	end
	stmt:finalize()
end
-----------------------------------------------------------------------------



-----------------------------------------------------------------------------
--添加索引
function DataSheet:add_keys(keys)
	for k,v in pairs(keys) do	
		local stmt = self.db:prepare("SELECT content FROM keys WHERE content = ?")
		stmt:bind_values(v)
		local count = 0;
		while (stmt:step() == sqlite3.ROW) do
			count = count + 1;
		end
		
		if count == 0 then
			 self.create_key:bind_names{  value =  v    }
			 self.create_key:step()
			 self.create_key:reset()		 
		end
		
		self.db:exec ('CREATE TABLE '..v.. ' (id INTEGER PRIMARY KEY,content)' )
		if self.insertStmt[v] then self.insertStmt[v]:finalize() end
		self.insertStmt[v] = self.db:prepare(' INSERT INTO '..v..' VALUES (:key, :value) ' )	
		stmt:finalize()
	end
end
-----------------------------------------------------------------------------


function DataSheet:read_farfields_file(path)				 
	print(path);
	self.data_list = {}
	self.data = file2table( path,"\t")	
	self.str = file2StrTable( path,"\t")	
	--print( self.str )
	local nheader = self.nheader;
	self.nangle = #self.str - nheader - 1 ;
	self.nfields = #self.str[nheader]-2;
	self.labels = self.str[nheader];
	
	number = "[ ]*[+-]?%d+[.]?%d*[Ee+-]*%d*";
	fields_type = '(.*)[%[(]';
	self.index = { theta = {labels={}, lable={}}, 
			     phi = {labels={}, lable={}}, 
			     freq = {labels={}, lable={}}, 
			     fields_types = {labels={}, lable={}}, };
	self.keys = {"theta","phi","freq","fields_types"};
	
	self:add_keys{"theta","phi","freq","fields_types"};
	self:create_data_table();

	
	for jj=1,self.nfields do
		local label = self.labels[2+jj];
		local freq =  string.match(label, number)
		local fts = string.match(label, fields_type)
		table.insert( self.index["freq"].labels, freq); 
		table.insert( self.index["fields_types"].labels, fts); 
			
		for ii=1,self.nangle do
			local theta = self.data[ii+nheader+1][1];
			local phi = self.data[ii+nheader+1][2];
			local key = self:get_key({theta,phi,freq,fts})
			self.data_list[key] = self.data[ii+nheader+1][2+jj];
			table.insert( self.index["theta"].labels, theta); 
			table.insert( self.index["phi"].labels, phi); 
		end
	end
	
	assert( self.nangle>0, "import theta empty" )
	assert( self.nfields>0, "import theta empty" )
	
	print( "Import fields succesfully, angle size:".. self.nangle .. ' fields size:' .. self.nfields ..' data size:' .. #self.data_list );
	
	-- for k,v in pairs(self.data_list) do
		 -- print(k.."="..v)
	-- end 
	
	self:compress_idx();
	
	table.sort(self.index.theta.label,function (a,b) return tonumber(a)<tonumber(b) end )
	table.sort(self.index.phi.label,function (a,b) return tonumber(a)<tonumber(b) end )
	table.sort(self.index.freq.label,function (a,b) return tonumber(a)<tonumber(b) end )

end	


return DataSheet;


